﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Xml;
using System.Text.RegularExpressions;
using Data.Abstract;
using Data.Configuration;
using System.Data.SQLite;
namespace Data
{
    public class DalVisitante
    {
        #region Propiedades
        Context _Context = new Context();
        SQLiteConnection _Conn;
        #endregion
        #region Procedimientos
        public DalVisitante() { }
        public DalVisitante(SQLiteConnection conn)
        {
            this._Conn = conn;
        }
        #endregion
        #region Metodos

        public void Insert(SimpleVisitante simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (!CheckTableExist())
                {
                    CreateTable();
                }
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT INTO Visitante (UserName,Password,FullName,FechaIngreso,Direccion,Numero,Localidad,Provincia)values(@UserName,@Password,@FullName,@FechaIngreso,@Direccion,@Numero,@Localidad,@Provincia)";

                cmd.Parameters.Add("@Id", DbType.Int32);
                cmd.Parameters.Add("@UserName", DbType.String);
                cmd.Parameters.Add("@Password", DbType.String);
                cmd.Parameters.Add("@FullName", DbType.String);
                cmd.Parameters.Add("@FechaIngreso", DbType.DateTime);
                cmd.Parameters.Add("@Direccion", DbType.String);
                cmd.Parameters.Add("@Numero", DbType.Int32);
                cmd.Parameters.Add("@Localidad", DbType.String);
                cmd.Parameters.Add("@Provincia", DbType.String);

                cmd.Parameters[0].Value = simple.Id;
                cmd.Parameters[1].Value = simple.UserName;
                cmd.Parameters[2].Value = simple.Password;
                cmd.Parameters[3].Value = simple.FullName;
                cmd.Parameters[4].Value = simple.FechaIngreso;
                cmd.Parameters[5].Value = simple.Direccion;
                cmd.Parameters[6].Value = simple.Numero;
                cmd.Parameters[7].Value = simple.Localidad;
                cmd.Parameters[8].Value = simple.Provincia;


                conn.Open();
                cmd.ExecuteNonQuery();

            }

        }
        public void Update(SimpleVisitante simple)
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                if (CheckTableExist())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Update Visitante set UserName = @UserName,Password = @Password,FullName = @FullName,FechaIngreso = @FechaIngreso, Direccion = @Direccion, Numero = @Numero, Localidad = @Localidad, Provincia = @Provincia where Id = @Id";

                    cmd.Parameters.Add("@Id", DbType.Int32);
                    cmd.Parameters.Add("@UserName", DbType.String);
                    cmd.Parameters.Add("@Password", DbType.String);
                    cmd.Parameters.Add("@FullName", DbType.String);
                    cmd.Parameters.Add("@FechaIngreso", DbType.DateTime);
                    cmd.Parameters.Add("@Direccion", DbType.String);
                    cmd.Parameters.Add("@Numero", DbType.Int32);
                    cmd.Parameters.Add("@Localidad", DbType.String);
                    cmd.Parameters.Add("@Provincia", DbType.String);


                    cmd.Parameters[0].Value = simple.Id;
                    cmd.Parameters[1].Value = simple.UserName;
                    cmd.Parameters[2].Value = simple.Password;
                    cmd.Parameters[3].Value = simple.FullName;
                    cmd.Parameters[4].Value = simple.FechaIngreso;
                    cmd.Parameters[5].Value = simple.Direccion;
                    cmd.Parameters[6].Value = simple.Numero;
                    cmd.Parameters[7].Value = simple.Localidad;
                    cmd.Parameters[8].Value = simple.Provincia;

                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void CreateTable()
        {
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "CREATE TABLE Visitante (Id INTEGER PRIMARY KEY AUTOINCREMENT, UserName varchar NOT NULL, Password varchar NOT NULL, FullName varchar NOT NULL,  FechaIngreso datetime NOT NULL, Direccion varchar NOT NULL, Numero int NOT NULL, Localidad varchar NOT NULL, Provincia varchar NOT NULL)";
                conn.Open();
                cmd.ExecuteNonQuery();
            }


        }
        //public DataTable GetByFecha(DateTime Fecha)
        //{
        //    DataTable Out = new DataTable();
        //    if (CheckTableExist())
        //    {

        //        using (SQLiteConnection conn = this._Conn)
        //        using (SQLiteCommand cmd = new SQLiteCommand(conn))
        //        {
        //            cmd.CommandType = CommandType.Text;
        //            cmd.CommandText = "Select * from Visitante where Fecha = @Fecha ";

        //            cmd.Parameters.Add("@Fecha", DbType.DateTime);
        //            cmd.Parameters[0].Value = Fecha;

        //            conn.Open();
        //            SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
        //            sda.Fill(Out);

        //        }
        //    }
        //    return Out;
        //}
        public DataTable GetAll()
        {
            DataTable Out = new DataTable();

            if (CheckTableExist())
            {
                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Visitante";
                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);
                }
            }
            return Out;
        }
       

        public DataTable GetByName(string  Name)
        {
            DataTable Out = new DataTable();

            if (CheckTableExist())
            {
                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Visitante where UserName = @UserName";

                    cmd.Parameters.Add("@UserName", DbType.String);
                    cmd.Parameters[0].Value = Name;

                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);
                }
            }
            return Out;
        }
        public DataTable GetById(int id)
        {
            DataTable Out = new DataTable();

            if (CheckTableExist())
            {
                using (SQLiteConnection conn = this._Conn)
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "Select * from Visitante where Id = @Id";

                    cmd.Parameters.Add("@Id", DbType.Int32);
                    cmd.Parameters[0].Value = id;

                    conn.Open();
                    SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                    sda.Fill(Out);
                }
            }
            return Out;
        }
        public bool CheckTableExist()
        {

            DataTable Out = new DataTable();
            using (SQLiteConnection conn = this._Conn)
            using (SQLiteCommand cmd = new SQLiteCommand(conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = " SELECT * FROM sqlite_master WHERE name = 'Visitante' AND type = 'table'";
                conn.Open();
                SQLiteDataAdapter sda = new SQLiteDataAdapter(cmd);
                sda.Fill(Out);
                bool SchemaOk = (Out.Rows.Count == 0) ? false : true;
                return SchemaOk;
            }
        }

        #endregion
    }
}